import pymysql
from pymysql import Error
import pandas as pd
def write_to_mysql(data_list):
    connect = pymysql.connect(
        host='localhost',
        port=3306,
        user='root',
        password='123456',
        database='dodan_movie',  # 确保数据库名正确
        charset='utf8mb4'
    )
    print('连接成功')
    try:
        with connect.cursor() as cursor:
            # 确保表名和字段名正确（这里改为 dodan_movie）
            sql = '''INSERT INTO dodan_movie 
                    (number, movie_name,director,first_actor,issue_date,country,genre,rating_num, comments)
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)'''
            for _, row in data_list.iterrows():
                # print(row)
                cursor.execute(sql, (row['number'],row['movie_name'],row['director'],row['first_actor'],row['issue_date'],row['country'],row['genre'],row['rating_num'],row['comments']))
        connect.commit()  # 修正变量名拼写错误（原代码写成了connection）
        print('数据插入成功')

    except Error as e:

        print(f"数据库操作失败: {e}")
        connect.rollback()  # 出现错误时回滚
    finally:
        if connect:
            connect.close()  # 关闭连接


if __name__ == '__main__':

    # 读取Excel文件并转换为列表格式
    df = pd.read_excel('backend\豆pa评分top250.xlsx')
    # print(df.columns)
    # 确保列顺序与SQL字段顺序一致，并转换为列表
    # data_list = movie_df.values.tolist()
    # print(df)
    write_to_mysql(df)
